import pymysql
import xlrd
from openpyxl import load_workbook

from config import BASE_DIR


def db_utils(sql: str, host="127.0.0.1", user="root", password="123456", db="test", port=3306):
    connection = pymysql.connect(host=host, user=user, password=password, db=db, port=port)
    cursor = connection.cursor()
    try:
        cursor.execute(sql)
        if sql.strip().lower().startswith("select"):
            result = cursor.fetchall()
            return result
        else:
            connection.commit()
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        cursor.close()
        connection.close()
# 数据库测试
res = db_utils("select * from register_data","123.57.30.81","Pytest","123456","pytest",3306)
# print(res)结果为元组套元组
sql = f"delete from tp_users where mobile = '15514091712'"
sql1 = f"select * from  tp_users where mobile = '13522963918'"
# db_utils(sql,"123.57.228.35","tpshop2.0","123456","tpshop2.0",3306)
res1 = db_utils(sql1,"123.57.228.35","tpshop2.0","123456","tpshop2.0",3306)
# print(res1)

#  读取excel数据并放到新的列表中,文件类型是.xls
def get_excel_xls(file_name:str, book_name:str,index:int = 0)->list[tuple]:
    file_path = BASE_DIR + "/data/" + file_name
    workbook = xlrd.open_workbook(file_path)  # 打开excel文件
    sheet = workbook.sheet_by_name(book_name)  #  获取表单
    new_list = []
    for i in range(index, sheet.nrows):
        new_list.append(tuple(sheet.row_values(i)))
    return new_list

# print(get_excel_xls('data.xls','login_data',1))

# 读取xlsx数据时如果为空则返回到python中就是None
# 需要将None转为空字符串
def get_excel_xlsx(file_name: str, book_name: str, row=2):
    file_path = BASE_DIR + "/data/" + file_name
    workbook = load_workbook(file_path)
    sheet = workbook[book_name]
    new_list = []
    for row_data in sheet.iter_rows(min_row=row, values_only=True):
        """
        列表里面套列表
        for cell in row_data 表示:遍历row_data值
        '' if cell is None else cell  表示:如果cell是空，则返回空字符串，否则返回cell
        """
        # cleaned_row = ['' if cell is None else cell for cell in row_data]
        # 列表里面套元组
        cleaned_row = tuple('' if cell is None else cell for cell in row_data)
        new_list.append(cleaned_row)
    workbook.close()
    return new_list
# print(get_excel_xlsx('data1.xlsx','login_data'))

# 读取数据库中的测试用例时如果为空则返回到python中就是None
# 需要将none转为空字符串
def get_db_cases(sql: str, host="127.0.0.1", user="root", password="123456", db="test", port=3306):
    db = db_utils(sql=sql,host=host, user=user,password=password, db=db,port=port)
    # print(db)
    new_list = []
    for row_data in db:
        cleaned_row = tuple('' if cell == 'none' else cell for cell in row_data)
        new_list.append(cleaned_row)
    return new_list

# print(get_db_cases("select * from register_data","123.57.30.81","Pytest","123456","pytest",3306))

# 读取Json数据
import json
# key是字典名称
def get_json_data(file_name: str,key: str):
    file_path = BASE_DIR + "/data/" + file_name
    # 默认为读
    with open(file_path, encoding="utf-8") as f:
        data = json.load(f)
    data = data[key]
    list1 = []
    for i in data:
        list1.append(tuple(i.values()))
        # print(i.values())
    return list1
# print(get_json_data("login.json","login"))